package com.bbs.utils;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

import com.bbs.model.testClass;
import jxl.read.biff.BiffException;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.xmlbeans.impl.piccolo.io.FileFormatException;

public class ExcelUtils {

    private static final String EXTENSION_XLS = "xls";
    private static final String EXTENSION_XLSX = "xlsx";
    private static final String[] FIELDS = new String[]{"1", "3"};//Excel列名


    /***
     * <pre>
     * 取得Workbook对象(xls和xlsx对象不同,不过都是Workbook的实现类)
     *   xls:HSSFWorkbook
     *   xlsx：XSSFWorkbook
     * @param filePath
     * @return
     * @throws IOException
     * </pre>
     */
    private static Workbook getWorkbook(String filePath) throws IOException {
        Workbook workbook = null;
        InputStream is = new FileInputStream(filePath);
        if (filePath.endsWith(EXTENSION_XLS)) {
            workbook = new HSSFWorkbook(is);
        } else if (filePath.endsWith(EXTENSION_XLSX)) {
            workbook = new XSSFWorkbook(is);
        }
        return workbook;
    }


    /**
     * 读取excel文件内容
     *
     * @param filePath
     * @throws FileNotFoundException
     * @throws FileFormatException
     */
    public static void readExcel(String filePath) throws FileNotFoundException, FileFormatException {
        // 获取workbook对象
        Workbook workbook = null;

        Map<String, Integer> rowMap = new HashMap<>();
        try {
            workbook = getWorkbook(filePath);
            // 读文件 一个sheet一个sheet地读取
            for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
                Sheet sheet = workbook.getSheetAt(numSheet);
                if (sheet == null) {
                    continue;
                }
                int firstRowIndex = sheet.getFirstRowNum();
                int lastRowIndex = sheet.getLastRowNum();

                // 读取首行 即,表头
                Row firstRow = sheet.getRow(firstRowIndex);
                for (int i = firstRow.getFirstCellNum(); i <= firstRow.getLastCellNum(); i++) {
                    Cell cell = firstRow.getCell(i);
                    String cellValue = getCellValue(cell, true);
                    //表头信息储存进Map中，包含表头名称和对应的index
                    if (StringUtils.isNotBlank(cellValue)) {
                        rowMap.put(cellValue, i);
                    }
                }

                // 读取数据行
                List<String> newStrArr = Arrays.asList(FIELDS);
                List<testClass> classes = new ArrayList<>();
                Set<Map.Entry<String, Integer>> set = rowMap.entrySet();
                for (int rowIndex = firstRowIndex + 1; rowIndex <= lastRowIndex; rowIndex++) {
                    classes.add((testClass)getObject(newStrArr,set,sheet,rowIndex));
                }
                System.out.print(classes);
            }


        } catch (Exception e) {
            e.printStackTrace();
        }
    }



    /**
     * 取单元格的值
     * @param cell       单元格对象
     * @param treatAsStr 为true时，当做文本来取值 (取到的是文本，不会把“1”取成“1.0”)
     * @return
     */
    private static String getCellValue(Cell cell, boolean treatAsStr) {
        if (cell == null) {
            return "";
        }
        if (treatAsStr) {
            // 虽然excel中设置的都是文本，但是数字文本还被读错，如“1”取成“1.0”
            // 加上下面这句，临时把它当做文本来读取
            cell.setCellType(Cell.CELL_TYPE_STRING);
        }

        if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            return String.valueOf(cell.getNumericCellValue());
        } else {
            return String.valueOf(cell.getStringCellValue());
        }
    }

    private static Object getObject(List<String> newStrArr,Set<Map.Entry<String, Integer>> set,Sheet sheet,int rowIndex){
        testClass testClass = new testClass();
        for (Map.Entry<String, Integer> entry : set) {
            //获取列名
            String value = entry.getKey().toString();
            //循环当前行每列数据，并且字段赋值
            for (int i = 0; i < newStrArr.size(); i++) {
                if (newStrArr.get(i).equals(value)) {
                    // 读取数据行
                    Row currentRow = sheet.getRow(rowIndex);// 当前行
                    Cell currentCell = currentRow.getCell(entry.getValue());// 当前单元格
                    String currentCellValue = getCellValue(currentCell, true);// 当前单元格的值

                    /*字段赋值*/
                    if (entry.getKey().equals("1")){
                        testClass.setId(Integer.parseInt(currentCellValue));
                    }
                    if (entry.getKey().equals("3")){
                        testClass.setValue(currentCellValue);
                    }
                }
            }
        }
        return testClass;
    }


    public static void main(String[] args) throws BiffException, IOException {

        String url = "d:\\aa.xlsx";
        readExcel(url);
    }
}
